"""
Python连接MySQL数据库完整CRUD操作示例
作者：zrk
日期：2024年

注意：数据库和表结构需要预先创建，请使用SQL脚本在数据库管理软件中创建
"""

import pymysql
from pymysql.cursors import DictCursor

# 数据库连接配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'python_learn_db',  # 请确保数据库已存在
    'charset': 'utf8mb4',
    'cursorclass': DictCursor  # 返回字典格式的结果
}

def create_connection():
    """创建数据库连接"""
    try:
        connection = pymysql.connect(**DB_CONFIG)
        print("✅ 数据库连接成功!")
        return connection
    except pymysql.Error as e:
        print(f"❌ 数据库连接失败: {e}")
        return None

def insert_student(name, age, gender='男', email=None):
    """插入学生数据 (Create)"""
    connection = create_connection()
    if not connection:
        return False
    
    try:
        cursor = connection.cursor()
        
        insert_sql = """
        INSERT INTO students (name, age, gender, email) 
        VALUES (%s, %s, %s, %s)
        """
        
        cursor.execute(insert_sql, (name, age, gender, email))
        connection.commit()
        student_id = cursor.lastrowid
        print(f"✅ 学生数据插入成功! ID: {student_id}")
        return student_id
        
    except pymysql.Error as e:
        print(f"❌ 插入数据失败: {e}")
        connection.rollback()
        return False
    finally:
        cursor.close()
        connection.close()

def select_all_students():
    """查询所有学生数据 (Read)"""
    connection = create_connection()
    if not connection:
        return []
    
    try:
        cursor = connection.cursor()
        
        select_sql = "SELECT * FROM students ORDER BY id"
        cursor.execute(select_sql)
        
        students = cursor.fetchall()
        print(f"📊 查询到 {len(students)} 条学生记录")
        return students
        
    except pymysql.Error as e:
        print(f"❌ 查询数据失败: {e}")
        return []
    finally:
        cursor.close()
        connection.close()

def select_student_by_id(student_id):
    """根据ID查询学生数据 (Read)"""
    connection = create_connection()
    if not connection:
        return None
    
    try:
        cursor = connection.cursor()
        
        select_sql = "SELECT * FROM students WHERE id = %s"
        cursor.execute(select_sql, (student_id,))
        
        student = cursor.fetchone()
        if student:
            print(f"🔍 找到学生: {student}")
        else:
            print("❌ 未找到该学生")
        return student
        
    except pymysql.Error as e:
        print(f"❌ 查询数据失败: {e}")
        return None
    finally:
        cursor.close()
        connection.close()

def update_student(student_id, name=None, age=None, gender=None, email=None):
    """更新学生数据 (Update)"""
    connection = create_connection()
    if not connection:
        return False
    
    try:
        cursor = connection.cursor()
        
        # 构建动态更新SQL
        update_fields = []
        params = []
        
        if name:
            update_fields.append("name = %s")
            params.append(name)
        if age is not None:
            update_fields.append("age = %s")
            params.append(age)
        if gender:
            update_fields.append("gender = %s")
            params.append(gender)
        if email is not None:  # 允许设置为空
            update_fields.append("email = %s")
            params.append(email)
        
        if not update_fields:
            print("⚠️ 没有提供更新字段")
            return False
        
        params.append(student_id)
        update_sql = f"UPDATE students SET {', '.join(update_fields)} WHERE id = %s"
        
        cursor.execute(update_sql, params)
        connection.commit()
        
        if cursor.rowcount > 0:
            print(f"✅ 学生数据更新成功! ID: {student_id}")
            return True
        else:
            print("❌ 未找到要更新的学生")
            return False
        
    except pymysql.Error as e:
        print(f"❌ 更新数据失败: {e}")
        connection.rollback()
        return False
    finally:
        cursor.close()
        connection.close()

def delete_student(student_id):
    """删除学生数据 (Delete)"""
    connection = create_connection()
    if not connection:
        return False
    
    try:
        cursor = connection.cursor()
        
        delete_sql = "DELETE FROM students WHERE id = %s"
        cursor.execute(delete_sql, (student_id,))
        connection.commit()
        
        if cursor.rowcount > 0:
            print(f"✅ 学生数据删除成功! ID: {student_id}")
            return True
        else:
            print("❌ 未找到要删除的学生")
            return False
        
    except pymysql.Error as e:
        print(f"❌ 删除数据失败: {e}")
        connection.rollback()
        return False
    finally:
        cursor.close()
        connection.close()

def display_students(students):
    """格式化显示学生数据"""
    if not students:
        print("📭 暂无学生数据")
        return
    
    print("\n📋 学生列表:")
    print("-" * 80)
    print(f"{'ID':<5} {'姓名':<10} {'年龄':<5} {'性别':<5} {'邮箱':<20} {'创建时间':<20}")
    print("-" * 80)
    
    for student in students:
        email = student.get('email', '无') or '无'
        created_at = str(student.get('created_at', ''))
        print(f"{student['id']:<5} {student['name']:<10} {student['age']:<5} {student['gender']:<5} {email:<20} {created_at:<20}")
    print("-" * 80)

def main():
    """主函数 - 演示完整的CRUD操作"""
    print("🚀 Python MySQL CRUD 操作演示")
    print("=" * 50)
    print("注意：请确保数据库和表结构已预先创建")
    print("=" * 50)
    
    # 1. 测试数据库连接
    connection = create_connection()
    if not connection:
        print("❌ 无法连接到数据库，请检查数据库是否存在")
        return
    connection.close()
    
    # 2. 插入数据 (Create)
    print("\n1. 插入学生数据:")
    student1_id = insert_student("张三", 20, "男", "zhangsan@example.com")
    student2_id = insert_student("李四", 22, "女", "lisi@example.com")
    student3_id = insert_student("王五", 19, "男")
    
    # 3. 查询所有数据 (Read)
    print("\n2. 查询所有学生:")
    students = select_all_students()
    display_students(students)
    
    # 4. 查询单个数据 (Read)
    print("\n3. 查询单个学生:")
    if student1_id:
        student = select_student_by_id(student1_id)
    
    # 5. 更新数据 (Update)
    print("\n4. 更新学生数据:")
    if student2_id:
        update_student(student2_id, age=23, email="lisi_updated@example.com")
    
    # 显示更新后的数据
    students = select_all_students()
    display_students(students)
    
    # 6. 删除数据 (Delete)
    print("\n5. 删除学生数据:")
    if student3_id:
        delete_student(student3_id)
    
    # 显示最终数据
    print("\n6. 最终学生数据:")
    students = select_all_students()
    display_students(students)
    
    print("\n🎉 CRUD操作演示完成!")

if __name__ == "__main__":
    main()